﻿using System;
using System.Collections.Generic;
using System.Data.SQLite;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Schedule
{
    class LinkDB
    {
        public List<Deadline> deadlines = Deadline.deadlines;
        public List<Course> courses = Course.courses;
        private string connStr = Properties.Settings.Default.ConnectionString;

        public void getDeadLines()
        {
            using (var connection = new SQLiteConnection(connStr))
            {
                connection.Open();
                SQLiteCommand cmd = new SQLiteCommand();
                cmd.Connection = connection;//把 SQLiteCommand的 Connection和SQLiteConnection 联系起来
                cmd.CommandText = "CREATE TABLE IF NOT EXISTS courseDDL(ddlID int, courseID long,task string,deadline string)";//输入SQL语句
                cmd.ExecuteNonQuery();//调用此方法运行

                cmd.CommandText = "SELECT * FROM courseDDL";
                using (var sr = cmd.ExecuteReader())
                {
                    while (sr.Read())
                    {
                        try
                        {
                            Deadline newDDL = new Deadline(sr.GetInt32(0), sr.GetInt64(1), sr.GetString(2), DateTime.Parse(sr.GetString(3)));
                            deadlines.Add(newDDL);
                        }
                        catch (Exception)
                        {//ddl名为纯数字
                            Deadline newDDL = new Deadline(sr.GetInt32(0), sr.GetInt64(1), sr.GetInt64(2).ToString(), DateTime.Parse(sr.GetString(3)));
                            deadlines.Add(newDDL);
                        }

                    }
                }
            }
        }

        public void addDeadline(Deadline newddl)
        {
            using (var connection = new SQLiteConnection(connStr))
            {
                connection.Open();
                SQLiteCommand cmd = new SQLiteCommand();
                cmd.Connection = connection;//把 SQLiteCommand的 Connection和SQLiteConnection 联系起来
                cmd.CommandText = "INSERT INTO courseDDL VALUES(" + newddl.DDLID + "," + newddl.CourseID.ToString() + ",'" + newddl.Task + "','" + newddl.DeadlineDate.ToString() + "')";
                cmd.ExecuteNonQuery();
            }
        }

        public void deleteDeadline(string courseID, string ddlName)
        {
            using (var connection = new SQLiteConnection(connStr))
            {
                connection.Open();
                SQLiteCommand cmd = new SQLiteCommand();
                cmd.Connection = connection;//把 SQLiteCommand的 Connection和SQLiteConnection 联系起来
                cmd.CommandText = "DELETE FROM courseDDL WHERE courseID=" + courseID + " AND task='" + ddlName + "';";//输入SQL语句
                cmd.ExecuteNonQuery();
            }
        }

        public void deleteDDLByCourseID(string courseID)
        {
            using (var connection = new SQLiteConnection(connStr))
            {
                connection.Open();
                SQLiteCommand cmd = new SQLiteCommand();
                cmd.Connection = connection;//把 SQLiteCommand的 Connection和SQLiteConnection 联系起来
                cmd.CommandText = "DELETE FROM courseDDL WHERE courseID=" + courseID + ";";//输入SQL语句
                cmd.ExecuteNonQuery();
            }
        }

        public void getCourses()
        {
            using (var connection = new SQLiteConnection(connStr))
            {
                connection.Open();
                SQLiteCommand cmd = new SQLiteCommand();
                cmd.Connection = connection;//把 SQLiteCommand的 Connection和SQLiteConnection 联系起来
                cmd.CommandText = "CREATE TABLE IF NOT EXISTS schedule(courseID long,name string,weekday int,beginLesson int,lastLesson int)";//输入SQL语句
                cmd.ExecuteNonQuery();//调用此方法运行

                cmd.CommandText = "SELECT * FROM schedule";
                using (var sr = cmd.ExecuteReader())
                {
                    while (sr.Read())
                    {
                        Course newCourse = new Course(sr.GetInt64(0), sr.GetString(1), sr.GetInt32(2), sr.GetInt32(3), sr.GetInt32(4));
                        courses.Add(newCourse);
                    }
                }
            }
        }

        public void deleteCourse(string courseID)
        {
            using (var connection = new SQLiteConnection(connStr))
            {
                connection.Open();
                SQLiteCommand cmd = new SQLiteCommand();
                cmd.Connection = connection;//把 SQLiteCommand的 Connection和SQLiteConnection 联系起来
                cmd.CommandText = "DELETE FROM schedule WHERE courseID=" + courseID + ";";//输入SQL语句
                cmd.ExecuteNonQuery();
            }
        }

        public void addCourse(Course newCourse)
        {
            using (var connection = new SQLiteConnection(connStr))
            {
                connection.Open();
                SQLiteCommand cmd = new SQLiteCommand();
                cmd.Connection = connection;//把 SQLiteCommand的 Connection和SQLiteConnection 联系起来
                cmd.CommandText = cmd.CommandText = "INSERT INTO schedule VALUES(" + newCourse.CourseID.ToString() + ",'" + newCourse.Name + "'," + newCourse.Weekday.ToString()
                    + "," + newCourse.BeginLesson.ToString() + "," + newCourse.LastLesson.ToString() + ")";
                cmd.ExecuteNonQuery();
            }
        }
    }
}
